ALTER                  function fn_missed_orders_report (

@time_stamp datetime,
@order_type int
)

returns @fn_results Table(So_No varchar(50),SalesOrderID int, LineItemNo int,CoolSchdDate datetime,ActSchdDate datetime,Status varchar(20),ProductID int, PartName varchar(20), CustomerID int, CustomerName varchar(65),SODate datetime,ReqDockDate datetime, ReqQty int,QtyShipped int, UnitCost float,Region varchar(20), RepOffice varchar(40),EndUser varchar(20),SalesPerson varchar(20),ChangeDate datetime, BookingMonth int, BookingYear int, BookingQuarter int, QuarterEnd int,EndCustomer varchar(50))

as
begin

	declare @max_change_no int 
	declare @change_date   datetime
	declare @booking_date  datetime 
	declare @CSLIT_ID int, @CSLITQtyshipped int
	declare @so_BK_Mt_I int, @so_Bk_Yr_I int, @so_Bk_Qt_I int, @so_Bk_Qt_End_I int 

	declare @results Table(So_No varchar(50),SalesOrderID int, LineItemNo int,CoolSchdDate datetime,ActSchdDate datetime,Status varchar(20),ProductID int, PartName varchar(20), CustomerID int, CustomerName varchar(65),SODate datetime,ReqDockDate datetime, ReqQty int,QtyShipped int, UnitCost float,Region varchar(20), RepOffice varchar(40),EndUser varchar(20),SalesPerson varchar(20),ChangeDate datetime, BookingMonth int, BookingYear int, BookingQuarter int, QuarterEnd int,EndCustomer varchar(50))

	declare @so_id int, @line_item_id int,@line_item_no int, @qty_shipped int,@act_qty int, @so_no varchar(50), @cust_ship_id int, @cst_id int, @product_id int,@cust_id int
	declare @cool_schd_date datetime,@act_date datetime, @so_date datetime,@cool_req_date datetime
	declare @unit_price float, @extended_price float
	declare @status varchar(20),@so_status varchar(20),@part_no varchar(20), @cust_name varchar(65),@cust_region varchar(20),@cust_rep_office varchar(40), @so_rep_office varchar(40), @so_end_user varchar(20),@so_sales_person varchar(20)	
	declare @temp_status varchar(20),@EndCustomer varchar(50)
				
	declare mainCursor cursor for 
		select salesOrder_ID, so_status,SalesOrder_No,Customer_ID, SO_Booking_Date,
		
		CASE WHEN ( SO_ODM_ID IS NULL OR SO_ODM_ID <= 0  )
			THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = sales_order.Customer_ID )
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = sales_order.SO_ODM_ID )
			END AS E_NAME,
		SO_Sales_Person,Regional_Rep,
		CASE WHEN ( SO_ENDCUSTOMER_ID IS NULL OR SO_ENDCUSTOMER_ID <= 0  )
			THEN (CASE WHEN ( SO_ODM_ID IS NULL OR SO_ODM_ID <= 0 )
				THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = sales_order.Customer_ID )
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = sales_order.SO_ODM_ID )
				END)
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = sales_order.SO_ENDCUSTOMER_ID)
			END AS EndCustomer
		from sales_order where sales_order.SO_Is_Sample = @order_type 

	open mainCursor
	fetch next from mainCursor into @so_id, @so_status, @so_no,@cust_id,@so_date,@so_end_user,@so_sales_person,@so_rep_office,@EndCustomer
	while @@fetch_status = 0
	begin
		if  @so_status = 'APPROVED'
		begin
			declare coolCursor cursor for
			select LineItem_Id, salesorder_ID, cool_line_item_no, cool_status,cool_schedule_date,cool_qty,Product_ID,  cool_requested_date,cool_unit_cost
				from customer_open_order_lineitem 
				where salesOrder_id = @so_id and cool_schedule_date is not null 
			open coolCursor 
			fetch next from coolCursor  into @line_item_id, @so_id,@line_item_no, @status, @cool_schd_date,@act_qty,@product_id,@cool_req_date,@unit_price
			while @@fetch_status = 0
			begin
				select @max_change_no = isnull( max(ChangeNo),0 ) from customer_change_order_lineitem where lineitem_id = @line_item_id
				if( @max_change_no = 0 ) 
				begin
					set @change_date = null
				end
				else
				begin
					select @change_date = CCO_Booking_Date from customer_change_order_lineitem where lineitem_id = @line_item_id and ChangeNo = @max_change_no
				end

				select @booking_date = CCO_Booking_Date from customer_change_order_lineitem where lineitem_id = @line_item_id and ChangeNo = @max_change_no

				if ( @cool_schd_date is not null )
				begin
					set @so_Bk_Yr_I = DATEPART(yyyy, @cool_schd_date)
					set @so_BK_Mt_I = DATEPART(m, @cool_schd_date)
					if (@so_BK_Mt_I >=1 and @so_BK_Mt_I <=3)
					begin
						set @so_Bk_Qt_I = 1						
						set @so_Bk_Qt_End_I =3
					end
					else if (@so_BK_Mt_I >=4 and @so_BK_Mt_I <=6)
					begin
						set @so_Bk_Qt_I = 2
						set @so_Bk_Qt_End_I =6
					end
					else if (@so_BK_Mt_I >=7 and @so_BK_Mt_I <=9)
					begin
						set @so_Bk_Qt_I = 3
						set @so_Bk_Qt_End_I =9
					end				
					else if (@so_BK_Mt_I >=10 and @so_BK_Mt_I <=12)
					begin
						set @so_Bk_Qt_I = 4
						set @so_Bk_Qt_End_I =12
					end
				end
				else
				begin
					set @so_BK_Mt_I = 0
					set @so_Bk_Qt_I = 0
					set @so_Bk_Yr_I = null
					set @so_Bk_Qt_End_I = 0
				end

				select @part_no=Part_Number from product where Product_ID = @product_id
				select @cust_name = C_Name,@cust_region=C_Region from customer where Customer_ID = @cust_id
				if @status = 'APPROVED'
				begin
					-- for the missed orders I had to convert the status of CSLIT from issued to shipped					
					set @temp_status = 'APPROVED'
					select @qty_shipped=(select sum(CSLIT_Qty_shipped) from customer_shipping_line_item_table where lineitem_id =@line_item_id and CSLIT_Status='shipped')
					if @qty_shipped is not null
					begin
						if (@act_qty > @qty_shipped) or (@act_qty = @qty_shipped)
						begin
							if (@act_qty > @qty_shipped)
							begin
								set @temp_status = 'PARTIALLY SHIPPED'
							end				
							else if (@act_qty = @qty_shipped) 
							begin
								set @temp_status = 'SHIPPED'
							end
									
							declare cslitCursor cursor for
								select distinct CustomerShipping_ID,CSLIT_ID from 
								customer_shipping_line_item_table where 
								lineitem_id =@line_item_id and CSLIT_status = 'shipped'
							open cslitCursor
							fetch next from cslitCursor into @cst_id,@CSLIT_ID	 
							while @@fetch_status = 0
							begin
								select @CSLITQtyshipped = CSLIT_Qty_shipped from customer_shipping_line_item_table where CSLIT_ID = @CSLIT_ID
								select @act_date=cst_ship_date from customer_shipping_table where customerShipping_id = @cst_id									
								if @act_date >@cool_schd_date
								begin										
									-- these are shipped but not in time									
									insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date, @temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)	
								end
								else if (@act_qty > @qty_shipped) and (@cool_schd_date < @time_stamp)
								begin
									-- these are partially shipped and the rest of the qty not yet shipped
									insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date, @temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)
								end								
								set @CSLITQtyshipped = null	
								set @act_date = null
								fetch next from cslitCursor into @cst_id,@CSLIT_ID	 
							end
							close cslitCursor
							deallocate cslitCursor					
						end
					end
					else if @qty_shipped is null
					begin
						declare @qty_shipped_temp int		
						set @qty_shipped_temp = 0
						if @cool_schd_date < @time_stamp
						begin
							-- these are not shipped and schd date passed by
							insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date,@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@qty_shipped_temp,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)						
						end
					end
						--This is for the line items which are partially shipped and for the remaining quantity unshipped
					/*if  (@qty_shipped is not null and @cool_schd_date < @time_stamp) 
					begin
						set @qty_shipped = 0
						insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,null,@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@qty_shipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I)
					end*/
				end
				else if @status='SHIPPED'
				begin
					set @temp_status = 'SHIPPED'
					declare shippedCursor cursor for
					select distinct CustomerShipping_ID,CSLIT_ID from customer_shipping_line_item_table where lineitem_id =@line_item_id and (CSLIT_Status ='SHIPPED' or CSLIT_Status= 'ISSUED')
					open shippedCursor
					fetch next from shippedCursor into @cust_ship_id,@CSLIT_ID 
					while @@fetch_status = 0
					begin
						--(select distinct CustomerShipping_ID from customer_shipping_line_item_table where lineitem_id =166)
						select @qty_shipped=(select sum(CSLIT_Qty_shipped) from customer_shipping_line_item_table where lineitem_id =@line_item_id and (CSLIT_Status='shipped' or CSLIT_Status='issued'))

						if (@act_qty > @qty_shipped)
						begin
							set @temp_status = 'PARTIALLY SHIPPED'
						end				
						else if (@act_qty = @qty_shipped) 
						begin
							set @temp_status = 'SHIPPED'
						end
						select @CSLITQtyshipped = CSLIT_Qty_shipped from customer_shipping_line_item_table where CSLIT_ID = @CSLIT_ID
						select @act_date=cst_ship_date from customer_shipping_table where customerShipping_id = @cust_ship_id 
																						
							if @act_date >@cool_schd_date 
							begin
								-- these are shipped but not in time
								insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date, @temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)
								--insert
							end
							else if (@act_date <@cool_schd_date) and (@act_qty > @qty_shipped) and (@cool_schd_date < @time_stamp)
							begin
								-- these are shipped but not in time
								insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date, @temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)
								--insert
							end
							set @qty_shipped = null
							set @act_date = null
							set @status = null
							set @cust_region=null
							set @so_rep_office=null
							
						fetch next from shippedCursor into @cust_ship_id ,@CSLIT_ID
					end
					close shippedCursor
					deallocate shippedCursor				
					
				end

				else if @status = 'HOLD'
				begin					
					select @qty_shipped=(select sum(CSLIT_Qty_shipped) from customer_shipping_line_item_table where lineitem_id =@line_item_id and CSLIT_Status='shipped')
						if @qty_shipped is not null
						begin
							if (@act_qty > @qty_shipped) or (@act_qty = @qty_shipped)
							begin
								if (@act_qty > @qty_shipped) 
								begin
									set @temp_status = 'PARTIALLY SHIPPED'
								end
								else if (@act_qty = @qty_shipped) 
								begin
									set @temp_status = 'SHIPPED'
										
								end
															
								declare cslitCursor cursor for
								select distinct CustomerShipping_ID,CSLIT_ID from 
								customer_shipping_line_item_table where 
								lineitem_id =@line_item_id and CSLIT_status = 'shipped'
								open cslitCursor
								fetch next from cslitCursor into @cst_id,@CSLIT_ID	 
								while @@fetch_status = 0
								begin
									select @CSLITQtyshipped = CSLIT_Qty_shipped from customer_shipping_line_item_table where CSLIT_ID = @CSLIT_ID
									select @act_date = cst_ship_date from customer_shipping_table where customerShipping_id = @cst_id

									if @act_date > @cool_schd_date
									begin
										-- these are shipped but not in time
										insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date,@temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)											
									end
									
									else if (@act_date <@cool_schd_date) and (@act_qty > @qty_shipped) and (@cool_schd_date < @time_stamp)
									begin											
										-- these are partially shipped and the rest of the qty not yet shipped
										insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date,@temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)											
									end
									
									set @CSLITQtyshipped = null										
									set @act_date = null
									fetch next from cslitCursor into @cst_id,@CSLIT_ID	 
								end
								close cslitCursor
								deallocate cslitCursor					
							end
						end
				
						-- these are not shipped and schd date passed by
						else if @qty_shipped is null
						begin
							--set @qty_shipped = 0
							set @qty_shipped_temp = 0
							if @cool_schd_date < @time_stamp
							begin										
								insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,null,@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@qty_shipped_temp,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)										
							end
						end
						--This is for the line items which are partially shipped and for the remaining quantity unshipped
						/*if (@qty_shipped is not null and @cool_schd_date < @time_stamp) 
						begin
							set @qty_shipped = 0
							insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,null,@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@qty_shipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I)
						end*/
							
				end
																											
				set @cust_region=null
				set @so_rep_office=null
				set @line_item_id = null 
				set @status = null -- Added by Basha (11/20/2003)
				
				fetch next from coolCursor into @line_item_id, @so_id,@line_item_no, @status, @cool_schd_date,@act_qty,@product_id,@cool_req_date,@unit_price
			end	
			close coolCursor 
			deallocate coolCursor 
		end
		else if @so_status = 'HOLD' or @so_status = 'PENDING' 
		begin
			
			declare holdCursor cursor for
			select LineItem_Id, salesorder_ID, cool_line_item_no, cool_status,cool_schedule_date,cool_qty,Product_ID,cool_requested_date,cool_unit_cost  
				from customer_open_order_lineitem 
				where salesOrder_id = @so_id and cool_schedule_date is not null 
			open holdCursor
			fetch next from holdCursor into @line_item_id, @so_id,@line_item_no, @status, @cool_schd_date,@act_qty,@product_id,@cool_req_date,@unit_price
			while @@fetch_status = 0
			begin				
				select @max_change_no = isnull( max(ChangeNo),0 ) from customer_change_order_lineitem where lineitem_id = @line_item_id
				if ( @max_change_no = 0 ) 
				begin
					set @change_date = null
				end
				else
				begin
					select @change_date = CCO_Booking_Date from customer_change_order_lineitem where lineitem_id = @line_item_id and ChangeNo = @max_change_no
				end
				select @booking_date = CCO_Booking_Date from customer_change_order_lineitem where lineitem_id = @line_item_id and ChangeNo = @max_change_no
				set @so_Bk_Yr_I = DATEPART(yyyy, @booking_date)
				set @so_BK_Mt_I = DATEPART(m, @booking_date)
				if (@so_BK_Mt_I >=1 and @so_BK_Mt_I <=3)
				begin
					set @so_Bk_Qt_I = 1
					set @so_Bk_Qt_End_I =3
				end
				else if (@so_BK_Mt_I >=4 and @so_BK_Mt_I <=6)
				begin
					set @so_Bk_Qt_I = 2
					set @so_Bk_Qt_End_I =6
				end
				else if (@so_BK_Mt_I >=7 and @so_BK_Mt_I <=9)
				begin
					set @so_Bk_Qt_I = 3
					set @so_Bk_Qt_End_I =9
				end	
				else if (@so_BK_Mt_I >=10 and @so_BK_Mt_I <=12)
				begin
					set @so_Bk_Qt_I = 4
					set @so_Bk_Qt_End_I =12
				end
				select @part_no=Part_Number from product where Product_ID = @product_id
				select @cust_name = C_Name,@cust_region=C_Region from customer where Customer_ID = @cust_id
				if @cool_schd_date < @time_stamp
				begin
					set @qty_shipped = 0
					if @status = 'APPROVED'
						begin
							set @temp_status = 'APPROVED'						
							select @qty_shipped=(select sum(CSLIT_Qty_shipped) from customer_shipping_line_item_table where lineitem_id =@line_item_id and CSLIT_Status='shipped')
							if @qty_shipped is not null
							begin
								if (@act_qty > @qty_shipped) or (@act_qty = @qty_shipped)
								begin
									if (@act_qty > @qty_shipped) 
									begin
										set @temp_status = 'PARTIALLY SHIPPED'										
									end
									else if (@act_qty = @qty_shipped) 
									begin
										set @temp_status = 'SHIPPED'										
									end
															
									declare cslitCursor cursor for
									select distinct CustomerShipping_ID,CSLIT_ID from 
									customer_shipping_line_item_table where 
									lineitem_id =@line_item_id and CSLIT_status = 'shipped'
									open cslitCursor
									fetch next from cslitCursor into @cst_id,@CSLIT_ID	 
									while @@fetch_status = 0
									begin										
										select @CSLITQtyshipped = CSLIT_Qty_shipped from customer_shipping_line_item_table where CSLIT_ID = @CSLIT_ID
										select @act_date = cst_ship_date from customer_shipping_table where customerShipping_id = @cst_id

										if @act_date > @cool_schd_date
										begin											
											-- these are shipped but not in time
											insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date,@temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)
										end
										else if (@act_date <@cool_schd_date) and (@act_qty > @qty_shipped) and (@cool_schd_date < @time_stamp)
										begin
											-- these are partially shipped and the rest of the qty not yet shipped
											insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date,@temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)
										end									
										set @CSLITQtyshipped = null										
										set @act_date = null
										fetch next from cslitCursor into @cst_id,@CSLIT_ID	 
									end
									close cslitCursor
									deallocate cslitCursor					
								end
							end
							-- these are not shipped and schd date passed by
							else if @qty_shipped is null
							begin
								set @qty_shipped_temp = 0
								if @cool_schd_date < @time_stamp
								begin	
									insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,null,@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@qty_shipped_temp,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)
								end
							end
							--This is for the line items which are partially shipped and for the remaining quantity unshipped
							if (@qty_shipped is not null and @cool_schd_date < @time_stamp)
							begin
								set @qty_shipped = 0								
								insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,null,@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@qty_shipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)
							end
						end						 	 						
						else if @status = 'HOLD'
						begin								
							-- This is for the line items which are partially shipped twice and then the remaining 							
							set @temp_status = 'APPROVED'							
							select @qty_shipped=(select sum(CSLIT_Qty_shipped) from customer_shipping_line_item_table where lineitem_id =@line_item_id and CSLIT_Status='shipped')
							if @qty_shipped is not null
							begin
								if (@act_qty > @qty_shipped) or (@act_qty = @qty_shipped)
								begin
									if (@act_qty > @qty_shipped) 
									begin
										set @temp_status = 'PARTIALLY SHIPPED'
									end
									else if (@act_qty = @qty_shipped) 
									begin
										set @temp_status = 'SHIPPED'										
									end
															
									declare cslitCursor cursor for
									select distinct CustomerShipping_ID,CSLIT_ID from 
									customer_shipping_line_item_table where 
									lineitem_id =@line_item_id and CSLIT_status = 'shipped'
									open cslitCursor
									fetch next from cslitCursor into @cst_id,@CSLIT_ID 
									while @@fetch_status = 0
									begin										
										select @CSLITQtyshipped = CSLIT_Qty_shipped from customer_shipping_line_item_table where CSLIT_ID = @CSLIT_ID
										select @act_date = cst_ship_date from customer_shipping_table where customerShipping_id = @cst_id

										if @act_date > @cool_schd_date
										begin											
											-- these are shipped but not in time
											insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date,@temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)
										end										
										else if (@act_date <@cool_schd_date) and (@act_qty > @qty_shipped) and (@cool_schd_date < @time_stamp)
										begin											
											-- these are partially shipped and the rest of the qty not yet shipped
											insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date,@temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)
										end									
									set @CSLITQtyshipped = null										
									set @act_date = null
									fetch next from cslitCursor into @cst_id,@CSLIT_ID	 
									end
									close cslitCursor
									deallocate cslitCursor					
								end
							end
							-- these are not shipped and schd date passed by
							else if @qty_shipped is null
							begin
								set @qty_shipped_temp = 0
								if @cool_schd_date < @time_stamp
								begin										
									insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,null,@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@qty_shipped_temp,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)										
								end
							end							
							--This is for the line items which are partially shipped and for the remaining quantity unshipped
							if (@qty_shipped is not null and @cool_schd_date < @time_stamp) 
							begin								
								set @qty_shipped = 0								
								insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,null,@status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@qty_shipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)
							end
						end
						else if  @status = 'SHIPPED'
						begin						
							set @temp_status = 'SHIPPED'
							declare shippedCursor cursor for
							select distinct CustomerShipping_ID,CSLIT_ID from customer_shipping_line_item_table where lineitem_id =@line_item_id and (CSLIT_Status ='SHIPPED' or CSLIT_Status= 'ISSUED')
							open shippedCursor
							fetch next from shippedCursor into @cust_ship_id,@CSLIT_ID
							while @@fetch_status = 0
							begin							
								select @qty_shipped=(select sum(CSLIT_Qty_shipped) from customer_shipping_line_item_table where lineitem_id =@line_item_id and (CSLIT_Status='shipped' or CSLIT_Status='issued'))
								if (@act_qty > @qty_shipped) or (@act_qty = @qty_shipped)
								begin
									if (@act_qty > @qty_shipped)
									begin
									
									set @temp_status = 'PARTIALLY SHIPPED'
									end				
									else if (@act_qty = @qty_shipped) 
									begin
										set @temp_status = 'SHIPPED'
									end
							end
								select @CSLITQtyshipped = CSLIT_Qty_shipped from customer_shipping_line_item_table where CSLIT_ID = @CSLIT_ID
								select @act_date=cst_ship_date from customer_shipping_table where customerShipping_id = @cust_ship_id 

								if @act_date > @cool_schd_date 
								begin
									-- these are shipped but not in time
									insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date, @temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)
								end	
								else if (@act_date <@cool_schd_date) and (@act_qty > @qty_shipped) and (@cool_schd_date < @time_stamp)
								begin									
									insert into @results values(@so_no,@so_id, @line_item_no,@cool_schd_date,@act_date, @temp_status,@product_id,@part_no,@cust_id,@cust_name,@so_date,@cool_req_date,@act_qty,@CSLITQtyshipped,@unit_price,@cust_region,@so_rep_office,@so_end_user,@so_sales_person,@change_date, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@EndCustomer)
								end
								set @qty_shipped = null
								set @act_date = null								
								set @cust_region=null
								set @so_rep_office=null
							fetch next from shippedCursor into @cust_ship_id,@CSLIT_ID								
							end
							close shippedCursor
							deallocate shippedCursor						
						end										
					end
					set @line_item_id = null 
					set @status = null -- Added by Basha (11/20/2003)
					set @cust_region=null
					set @so_rep_office=null
					fetch next from holdCursor into @line_item_id, @so_id,@line_item_no, @status, @cool_schd_date,@act_qty,@product_id,@cool_req_date,@unit_price
				end
				close holdCursor
				deallocate holdCursor
			end
				
			set @so_id = null
			set @so_end_user=null
			set @so_sales_person=null
			set @EndCustomer = null
			fetch next from mainCursor into @so_id, @so_status, @so_no,@cust_id,@so_date,@so_end_user,@so_sales_person,@so_rep_office,@EndCustomer
			end
		close mainCursor
		deallocate mainCursor
	insert @fn_results select * from @results order by  So_No
  	return
  end
  

	

 
